SQL Server utilizing Discretionary Access Control (DAC) must enforce a policy that includes or excludes access to the granularity of a single user.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-41203	SQL2-00-011100	SV-53670r1_rule		Medium

Description
DAC is based on the notion that individual users are "owners" of objects and therefore have discretion over who should be authorized to access the object, and in which mode (e.g., read or write). Ownership is usually acquired as a consequence of creating the object or via specified ownership assignment. DAC allows the owner to determine who will have access to objects they control. An example of DAC includes user-controlled file permissions. Including or excluding access to the granularity of a single user means providing the capability to either allow or deny access to objects (e.g., files, folders) on a per single-user basis. Databases using DAC must have the ability for the owner of an object or information to assign or revoke rights to view or modify the object or information. If the owner of an object or information does not have rights to exclude access to an object or information at a user level, users may gain access to objects and information they are not authorized to view/modify.

Description

DAC is based on the notion that individual users are "owners" of objects and therefore have discretion over who should be authorized to access the object, and in which mode (e.g., read or write). Ownership is usually acquired as a consequence of creating the object or via specified ownership assignment. DAC allows the owner to determine who will have access to objects they control. An example of DAC includes user-controlled file permissions. Including or excluding access to the granularity of a single user means providing the capability to either allow or deny access to objects (e.g., files, folders) on a per single-user basis. Databases using DAC must have the ability for the owner of an object or information to assign or revoke rights to view or modify the object or information. If the owner of an object or information does not have rights to exclude access to an object or information at a user level, users may gain access to objects and information they are not authorized to view/modify.

STIG	Date
Microsoft SQL Server 2012 Database Instance Security Technical Implementation Guide	2014-01-17

Details

Check Text ( C-47793r2_chk )
Check for direct user assignment to server permissions by running the following script: /******************************************************************************** LIST ALL DIRECT SERVER PERMISSIONS TO ANY ACCOUNT EXCEPT SYSTEM ADMINISTRATOR accounts. DO NOT LIST ROLES. *******************************************************************************/ DECLARE @admin_Account_name sysname SET @admin_Account_name = 'NO admin ACCOUNT found' DECLARE @server_name sysname SET @server_name = 'NO Server found' SELECT @server_name = name FROM sys.servers WHERE server_id = 0 SET @admin_Account_name = @server_name + '\Administrator' SELECT pe.grantee_principal_id , pr.type AS 'Grantee_Type' , pr.name AS 'Grantee_Name' , pe.type , pe.permission_name , pe.state , pe.state_desc FROM sys.server_permissions pe JOIN sys.server_principals pr ON pe.grantee_principal_id = pr.principal_id JOIN sys.server_principals ps ON pe.grantor_principal_id = ps.principal_id LEFT JOIN sys.server_principals us ON us.principal_id = pe.major_id WHERE pr.type IN ('K', 'S', 'U') AND pe.grantee_principal_id > 10 AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##', 'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER', 'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt') AND NOT pr.name = @admin_Account_name ORDER BY CASE pr.type WHEN 'K' THEN 1 WHEN 'S' THEN 2 WHEN 'U' THEN 3 ELSE 4 END If any user account list indicates direct access to any server permission, this is a finding. Obtain the list of available user-defined server roles from system documentation. Obtain the list of available user-defined server roles from the SQL Server system by running the following script: /****************************************************************************** LIST ALL INDIRECT (via ROLES) ACCESS TO THE SERVER PERMISSION. *********************************************************************************/ DECLARE @admin_Account_name sysname SET @admin_Account_name = 'NO admin ACCOUNT found' DECLARE @server_name sysname SET @server_name = 'NO Server found' SELECT @server_name = name FROM sys.servers WHERE server_id = 0 SET @admin_Account_name = @server_name + '\Administrator' SELECT pe.grantee_principal_id , pr.type AS 'Grantee_Type' , pr.name AS 'Grantee_Name' , pe.type , pe.permission_name , pe.state , pe.state_desc FROM sys.server_permissions pe JOIN sys.server_principals pr ON pe.grantee_principal_id = pr.principal_id JOIN sys.server_principals ps ON pe.grantor_principal_id = ps.principal_id LEFT JOIN sys.server_principals us ON us.principal_id = pe.major_id WHERE pr.type IN ('R') AND pe.grantee_principal_id > 10 AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##', 'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER', 'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt') AND NOT pr.name = @admin_Account_name ORDER BY CASE pe.state WHEN 'D' THEN 1 WHEN 'W' THEN 2 WHEN 'G' THEN 3 ELSE 4 END If any listed user-defined roles are not found in the system documentation, this is a finding. Determine from system documentation if SQL Server has DAC policies implemented. If not, this is not a finding. Obtain the list of DAC system owners/users from the system documentation. Check all SQL Server user-defined server roles from the listing that assign DAC system owners permissions to exclude a single user from the DAC owners system. Repeat steps for each user-defined server role. Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'. If any roles are found that aren't enforcing access at the single user level, this is a finding.

Check Text ( C-47793r2_chk )

Check for direct user assignment to server permissions by running the following script:
/**********************************************************************************
LIST ALL DIRECT SERVER PERMISSIONS TO ANY ACCOUNT EXCEPT
SYSTEM ADMINISTRATOR accounts. DO NOT LIST ROLES.
***********************************************************************************/
DECLARE @admin_Account_name sysname
SET @admin_Account_name = 'NO admin ACCOUNT found'
DECLARE @server_name sysname
SET @server_name = 'NO Server found'

SELECT @server_name = name FROM sys.servers
WHERE server_id = 0
SET @admin_Account_name = @server_name + '\Administrator'

SELECT pe.grantee_principal_id
, pr.type AS 'Grantee_Type'
, pr.name AS 'Grantee_Name'
, pe.type
, pe.permission_name
, pe.state
, pe.state_desc
FROM sys.server_permissions pe
JOIN sys.server_principals pr
ON pe.grantee_principal_id = pr.principal_id
JOIN sys.server_principals ps
ON pe.grantor_principal_id = ps.principal_id
LEFT JOIN sys.server_principals us
ON us.principal_id = pe.major_id
WHERE pr.type IN ('K', 'S', 'U')
AND pe.grantee_principal_id > 10
AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##',
'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER',
'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt')
AND NOT pr.name = @admin_Account_name
ORDER BY CASE pr.type
WHEN 'K' THEN 1
WHEN 'S' THEN 2
WHEN 'U' THEN 3
ELSE 4
END

If any user account list indicates direct access to any server permission, this is a finding.

Obtain the list of available user-defined server roles from system documentation.

Obtain the list of available user-defined server roles from the SQL Server system by running the following script:
/**********************************************************************************
LIST ALL INDIRECT (via ROLES) ACCESS TO THE SERVER PERMISSION.
***********************************************************************************/
DECLARE @admin_Account_name sysname
SET @admin_Account_name = 'NO admin ACCOUNT found'
DECLARE @server_name sysname
SET @server_name = 'NO Server found'

SELECT @server_name = name FROM sys.servers
WHERE server_id = 0
SET @admin_Account_name = @server_name + '\Administrator'

SELECT pe.grantee_principal_id
, pr.type AS 'Grantee_Type'
, pr.name AS 'Grantee_Name'
, pe.type
, pe.permission_name
, pe.state
, pe.state_desc
FROM sys.server_permissions pe
JOIN sys.server_principals pr
ON pe.grantee_principal_id = pr.principal_id
JOIN sys.server_principals ps
ON pe.grantor_principal_id = ps.principal_id
LEFT JOIN sys.server_principals us
ON us.principal_id = pe.major_id
WHERE pr.type IN ('R')
AND pe.grantee_principal_id > 10
AND NOT pr.name IN ('##MS_PolicyEventProcessingLogin##', '##MS_PolicyTsqlExecutionLogin##',
'NT AUTHORITY\NETWORK SERVICE', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER',
'NT SERVICE\SQLSERVERAGENT', 'NT SERVICE\SQLWriter', 'NT SERVICE\Winmgmt')
AND NOT pr.name = @admin_Account_name
ORDER BY CASE pe.state
WHEN 'D' THEN 1
WHEN 'W' THEN 2
WHEN 'G' THEN 3
ELSE 4
END

If any listed user-defined roles are not found in the system documentation, this is a finding.

Determine from system documentation if SQL Server has DAC policies implemented. If not, this is not a finding.

Obtain the list of DAC system owners/users from the system documentation.

Check all SQL Server user-defined server roles from the listing that assign DAC system owners permissions to exclude a single user from the DAC owners system. Repeat steps for each user-defined server role.

Navigate to SQL Server Management Studio >> Object Explorer >> <'SQL Server name'. If any roles are found that aren't enforcing access at the single user level, this is a finding.

Fix Text (F-46595r2_fix)
Add the user-defined server role to the system documentation. Add the user as a member of the user-defined server role within the system documentation. Remove the user from direct access to server permission by running the following script: USE master REVOKE <'server permission name'> TO <'account name'> CASCADE Remove the user from user-defined role access by running the following script: USE master ALTER SERVER ROLE [<'server role name'>] DROP MEMBER <'user name'> Remove server role permission from the user-defined server role by running the following script: USE master REVOKE <'server role name'> TO [<'server role name'>]

Fix Text (F-46595r2_fix)

Add the user-defined server role to the system documentation.

Add the user as a member of the user-defined server role within the system documentation.

Remove the user from direct access to server permission by running the following script:
USE master
REVOKE <'server permission name'> TO <'account name'> CASCADE

Remove the user from user-defined role access by running the following script:
USE master
ALTER SERVER ROLE [<'server role name'>] DROP MEMBER <'user name'>

Remove server role permission from the user-defined server role by running the following script:
USE master
REVOKE <'server role name'> TO [<'server role name'>]